package com.crazy.crazyuserservice.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.crazy.crazymodel.model.entity.BankCard;
import com.crazy.crazymodel.model.entity.Customer;
import org.apache.ibatis.annotations.*;


import java.math.BigDecimal;
import java.sql.SQLException;
import java.util.ArrayList;

@Mapper
public interface ClientMapper extends BaseMapper<Customer> {

    /**
     * 获取全部用户表
     * @return
     */
    @Select("select * from customer")
    ArrayList<Customer> getAllCustomer() throws SQLException;

    /**
     * 根据用户id查询用户
     * @param id
     * @return
     */
    @Select("select * from customer where customerID = #{id}")
    Customer getByCustomerID(Integer id) throws SQLException;

    /**
     * 根据用户姓名模糊查询所有用户
     * @param name
     * @return
     */
    @Select("select * from customer where name like concat('%', #{name}, '%')")
    ArrayList<Customer> getByCustomerName(String name) throws SQLException;

    /**
     * 根据银行卡id查询银行卡
     * @param id
     * @return
     */
    @Select("select * from bank_card where bankCardID = #{id}")
    BankCard getByBankCardID(Integer id) throws SQLException;

    /**
     * 根据银行卡号查询银行卡
     * @param number
     * @return
     */
    @Select("select * from bank_card where bankCardNumber = #{number}")
    BankCard getByBankCardNumber(String number) throws SQLException;

    /**
     * 根据用户id查询所有银行卡
     * @param id
     * @return
     */
    @Select("select * from bank_card where customerID = #{id}")
    ArrayList<BankCard> getBankCardByCustomerID(Integer id) throws SQLException;

    /**
     * 根据证件号码查询用户ID
     * @param idNumber
     * @return
     */
    @Select("select customerID from customer where idNumber = #{idNumber}")
    Integer getCustomerIdByCardNumber(String idNumber) throws SQLException;

    /**
     * 检查银行卡是否拥有份额
     * @param card
     */
    @Select("Select positionId from position where bankCardNumber = #{card}")
    ArrayList<Integer> checkCarddel(String card) throws SQLException;

    /**
     * 插入用户数据
     * @param customer
     */
    @Insert("insert into customer (name, customerType, idNumber, occupation, incomeLevel, totalAssets, address, contactInfo, riskToleranceScore, openTime) " +
            "values " +
            "(#{name},#{customerType},#{idNumber},#{occupation},#{incomeLevel},#{totalAssets},#{address},#{contactInfo},#{riskToleranceScore},#{openTime})")
    void insertAccount(Customer customer) throws SQLException;

    /**
     * 插入银行卡数据
     * @param card
     */
    @Insert("insert into bank_card (bankCardNumber, type, bankName, bankBranch, expiryDate, cvv, customerID, isPrimary, status, createTime, updateTime) " +
            "values " +
            "(#{bankCardNumber},#{type},#{bankName},#{bankBranch},#{expiryDate},#{cvv},#{customerID},#{isPrimary},#{status},#{createTime},#{updateTime})")
    void insertBankCard(BankCard card) throws SQLException;

    /**
     * 插入银行卡余额及净值记录
     * @param card
     */
    @Insert("insert into card_balance (bankCardNumber, balance) " +
            "values " +
            "(#{bankCardNumber}, #{balance})")
    void insertBankCardBalance(BankCard card) throws SQLException;

    /**
     * 银行卡充值
     * @param card
     */
    @Update("Update card_balance " +
            "set balance = balance + #{balance} " +
            "where bankCardNumber = #{bankCardNumber}")
    void recharge(BankCard card) throws SQLException;

    /**
     * 获得银行卡余额
     * @param bankcard
     */
    @Select("Select balance from card_balance " +
            "where bankCardNumber = #{bankcard}")
    BigDecimal getMoney(String bankcard) throws SQLException;

    /**
     * 更新用户信息
     * @param customer
     */
    @Update("Update customer " +
            "set customerType = COALESCE(#{customerType}, customerType), " +
            "name = COALESCE(#{name}, name), " +
            "idNumber = COALESCE(#{idNumber}, idNumber), " +
            "occupation = COALESCE(#{occupation}, occupation), " +
            "incomeLevel = COALESCE(#{incomeLevel}, incomeLevel), " +
            "address = COALESCE(#{address}, address), " +
            "contactInfo = COALESCE(#{contactInfo}, contactInfo), " +
            "riskToleranceScore = COALESCE(#{riskToleranceScore}, riskToleranceScore) " +
            "where customerID = #{customerID}")
    void updateAccount(Customer customer) throws SQLException;

    /**
     * 更新银行卡信息
     * @param card
     */
    @Update("Update bank_card " +
            "set status = COALESCE(#{status}, status), " +
            "bankName = COALESCE(#{bankName}, bankName), " +
            "bankBranch = COALESCE(#{bankBranch}, bankBranch), " +
            "updateTime = COALESCE(#{updateTime}, updateTime) " +
            "where bankCardID = #{bankCardID}")
    void updateBankCard(BankCard card) throws SQLException;

    /**
     * 根据用户id删除用户
     * @param id
     */
    @Delete("delete from customer where customerID = #{id}")
    void deleteAccountById(Integer id) throws SQLException;

    /**
     * 根据银行卡id删除银行卡
     * @param id
     */
    @Delete("delete from bank_card where bankCardID = #{id}")
    void deleteBankCardById(Integer id) throws SQLException;

    /**
     * 根据银行卡号删除银行卡
     * @param bankcard
     */
    @Delete("delete from bank_card where bankCardNumber = #{bankcard}")
    void deleteBankCardByCard(String bankcard) throws SQLException;

    /**
     * 根据用户id删除所有银行卡
     * @param id
     */
    @Delete("delete from bank_card where customerID = #{id}")
    void deleteAllCardsByAccountId(Integer id) throws SQLException;
}
